library TDB;


{

This DLL is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This DLL is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

This DLL uses Cristian Nicola's Direct mysqlobjects. See http://sourceforge.net/projects/directsql



History:
   27. 09. 2003      -The first version with flat file db
   29. 09. 2003      -Changed Strings to  PChars
   30. 09. 2003      -Seems to work
   02. 10. 2003      -GetDayData records allocated on main program. Function
                      returns the number of records that is needed more
   02. 11. 2003      -GetDataPath removed from DLL. It's now back in pub.pas

   01. 12. 2003      -Exported function's parameters changed
   06. 12. 2003      -GetNode, InserNode, SaveAlarm, SaveTemp seems to work in mySQL version
   07. 12. 2003      -Everything seems to work...
   12. 12. 2003      -Added CriticalSection
   30. 12. 2003      -Decimal separator in SQL-clause can not be ","  so it
                      will be changed to "." before temperature conversions.
                      Also, the Date and Time separators must be controlled.
   01. 01. 2004      -TIMESTAMP requested to be TIME and DATE
   05. 01. 2004      -TIME and DATE was no good, revert back to TIMESTAMP
   22. 03. 2005      -TIMESTAMP in mySQL 4.1 is changed, support for it
                      NOTE! This version does NOT support new password encryption
}
uses
  SysUtils,
  Classes,
  inifiles,
  pub,
  windows,
  dialogs,
  uMysqlClient,
  uMysqlCT,
  uMysqlHelpers,
  umysqlvio;

{$R *.RES}
var
    FMySQL           : TMySQLClient;
    FResult          : TMysqlResult;
    FNodeID          : Integer;
    FNodeName        : String;
    FNodeDesc        : String;
    FAlarmKeepTime   : Integer;
    FLogLevel        : Integer;
    FCriticalSection : TRTLCriticalSection;
    FSQLVersion41    : Boolean;

//function SQLTimeStampToDateTime(const sTime, sDate : String) : TDateTime;
function SQLTimeStampToDateTime(sTimeStamp : String) : TDateTime;
var sCurrDateFormat,
    sCurrTimeFormat  : String;
    cCurrDateSep,
    cCurrTimeSep     : Char;
begin
   //Backup current values
   sCurrDateFormat := ShortDateFormat;
   sCurrTimeFormat := ShortTimeFormat;
   cCurrDateSep := DateSeparator;
   cCurrTimeSep := TimeSeparator;

//   DateSeparator := '/';
   DateSeparator := '-';
   TimeSeparator := ':';
//   ShortDateFormat := 'YYYY/MM/DD';
   ShortDateFormat := 'YYYY-MM-DD';
   ShortTimeFormat := 'hh:mm:ss';

   //Time = hh:mm:ss
   //Date = YYYY-MM-DD
   //TIMESTAMP YYYYMMDDHHMMSS       4.0
   //TIMESTAMP YYYY-MM-DD HH:MM:SS  4.1
{
   result := StrToDateTime(Copy(sDate,1,4)+'/'+
                           Copy(sDate,6,2)+'/'+
                           Copy(sDate,9,2)+' '+
                           Copy(sTime,1,2)+':'+
                           Copy(sTime,4,2)+':'+
                           Copy(sTime,7,2)
                           );
}
   if FSQLVersion41 then
      result := StrToDateTime(sTimeStamp)
   else
      result := StrToDateTime(Copy(sTimeStamp,1,4)+'-'+   //YYYY
                              Copy(sTimeStamp,5,2)+'-'+   //MM
                              Copy(sTimeStamp,7,2)+' '+   //DD
                              Copy(sTimeStamp,9,2)+':'+   //HH
                              Copy(sTimeStamp,11,2)+':'+  //MM
                              Copy(sTimeStamp,13,2)       //SS
                              );


   //Restore formats
   ShortDateFormat := sCurrDateFormat;
   ShortTimeFormat := sCurrTimeFormat;
   DateSeparator := cCurrDateSep;
   TimeSeparator := cCurrTimeSep;
end;

function DoSQL (sSQL : String; bStore : Boolean) : boolean;
var bExecOK : Boolean;
begin
   bExecOK := FALSE;
   if assigned(FResult) then
      FreeAndNil(FResult);

   if not FMySQL.Connected then
   begin
      if FLogLevel > 0 then
         pub.AddLog('DB was not connected. Trying again...','.\SQLLOG.txt',500);
      FMySQL.connect;
   end;

   FResult := FMySQL.query(sSQL,bStore,bExecOK);
   if FLogLevel > 9 then
      pub.AddLog('Execute query: '+sSQL,'.\SQLLOG.txt',500);

   if (not bExecOK) and FMySQL.ShouldReconnect then    //Try again
   begin
      if FLogLevel > 0 then
         pub.AddLog('Should reconnect...','.\SQLLOG.txt',500);
      FMySQL.reconnect;
      if assigned(FResult) then
         FreeAndNil(FResult);
      FResult := FMySQL.query(sSQL,bStore,bExecOK);
   end;

   result := bExecOK;

   if (not result) and (FLogLevel > 0) then
      pub.AddLog('Error: '+sSQL + chr(10)+'MySQL error:' + FMySQL.LastError,'.\SQLLOG.txt',500);

end;

function DoCheckDate(iNodeID, iSensorID : Integer; DateOfData : TDateTime) : Boolean ;
var sSQL : string;
begin
   // Check if there are any data on requested date
   sSQL := 'select count(data_sensorid) from data where '+
           'data_nodeid='+ IntToStr(iNodeID)+ ' and '+
           'data_sensorid=' + IntToStr(iSensorID)+ ' and '+
           'data_time like "' + FormatDateTime('yyyymmdd%',DateOfData)+'"';
   result := DoSQL(sSQL,FALSE);

   sSQL := String(FResult.FieldValueByName('count(data_sensorid)'));
   if StrToInt(sSQL) > 0 then
      result := TRUE
   else
      result := FALSE;

end;



procedure DoInsertAlarm (iNodeID, iSensorID, iAlarmType : Integer; pAlarm : PChar; dtAlarmDateTime : TDateTime);
var sSQL  : String;
begin
   sSQL := 'insert into alarm values ('+IntToStr(iNodeID)+','+
            IntToStr(iSensorID)+','+
            IntToStr(iAlarmType)+',"'+
            String(pAlarm)+'",'+
            FormatDateTime('yyyymmddhhnnss',dtAlarmDateTime)+')';
//            FormatDateTime('hhnnss',dtAlarmDateTime)+','+      // Time
//            FormatDateTime('yyyymmdd',dtAlarmDateTime)+')'; // Date

   DoSQL(sSQL, FALSE);

   // Keep only number of FKeepAlarm days, delete rest
//   sSQL := 'delete from alarm where alarm_date<' +
//           FormatDateTime('yyyymmdd',dtAlarmDateTime - FAlarmKeepTime)+
   sSQL := 'delete from alarm where alarm_time<' +
           FormatDateTime('yyyymmddhhnnss',dtAlarmDateTime - FAlarmKeepTime)+
           ' and alarm_nodeid='+ IntToStr(FNodeID);

   DoSQL(sSQL, FALSE);
end;

procedure DoGetAlarms (iNodeID : Integer; slAlarms : TStrings);
var sSQL,
    sTmp : String;
    i    : Integer;
begin
{
   sSQL := 'select alarm_text, alarm_time, alarm_date from alarm where alarm_nodeid = '+
            IntToStr(FNodeID)+
           ' order by alarm_date, alarm_time';
}
   sSQL := 'select alarm_text, alarm_time from alarm where alarm_nodeid = '+
            IntToStr(FNodeID)+
           ' order by alarm_time';

   DoSQL(sSQL,TRUE);
   if FResult.RowsCount > 0 then
      for i := 1 to FResult.RowsCount do
      begin
         // Set sTmp to DD.MM.YYYY HH:MM:SS
{
         sTmp := FormatDateTime('dd.mm.yyyy hh:nn:ss',
                     SQLTimeStampToDateTime(
                        String(FResult.FieldValueByName('alarm_time')),
                        String(FResult.FieldValueByName('alarm_date'))
                     )
                 );
}
         sTmp := FormatDateTime('dd.mm.yyyy hh:nn:ss',
                                 SQLTimeStampToDateTime(
                                 FResult.FieldValueByName('alarm_time'))
                               );

         slAlarms.Add(sTmp + ' '+FResult.FieldValueByName('alarm_text'));
         FResult.Next;
      end;

end;


procedure DoInsertNode (sNodename, sNodeDescription : PChar);
var sSQL  : String;
begin
   sSQL := 'insert into nodes values (0,"'+string(sNodeName)+'","'+ string(sNodeDescription)+'")';
   DoSQL(sSQL, FALSE);
end;

function DoGetNode (sNodeName : PChar) : Integer;
var sSQL : String;
begin
   result := -1;
   sSQL := 'select node_id from nodes where node_name = "'+string(sNodeName)+'"';
   DoSQL (sSQL, TRUE);

   if FResult.RowsCount > 0 then
   begin
      result := StrToInt(FResult.FieldValueByName('node_id'));
   end;

   if FLogLevel > 9 then
      pub.AddLog('Got nodeid '+ IntToStr(result),'.\SQLLOG.txt',500);
end;


function DoSaveTemp(iNodeID, iSensorID : Integer; fTemp : Real; dtDateTime : TDateTime) : Integer;
var sSQL : string;
    cDS  : Char;
begin
   result := 1;

   cDS := DecimalSeparator;
   DecimalSeparator := '.';
   sSQL := 'insert into data values ('+IntToStr(iSensorID)+','+
               IntToStr(iNodeID)+','+
               FormatDateTime('yyyymmddhhnnss',dtDateTime)+','+
//               FormatDateTime('hhnnss',dtDateTime)+','+
//               FormatDateTime('yyyymmdd',dtDateTime)+','+
               FormatFloat('0.00',fTemp)+')';
   DecimalSeparator := cDS;

   if DoSQL(sSQL,FALSE) then
      result := 0;

end;

procedure DoGetTemp (iNodeID, iSensorID : Integer; dtBegin, dtEnd : TDateTime);
var sSQL    : string;

begin
{
   This is why DATE and TIME is not good.
   sSQL := 'select data_time, data_date, data_temp from data where '+
           'data_nodeid='+ IntToStr(iNodeID) +
           ' and data_sensorid=' + IntToStr(iSensorID) +
           ' and data_date>=' + FormatDateTime('yyyymmdd',dtBegin) +
           ' and data_date<=' + FormatDateTime('yyyymmdd',dtEnd) +
           ' order by data_date, data_time';
}
   sSQL := 'select data_time, data_temp from data where '+
           'data_nodeid='+ IntToStr(iNodeID) +
           ' and data_sensorid=' + IntToStr(iSensorID) +
           ' and data_time>' + FormatDateTime('yyyymmddhhnnss',dtBegin) +
           ' and data_time<=' + FormatDateTime('yyyymmddhhnnss',dtEnd) +
           ' order by data_time';

   DoSQL (sSQL,TRUE);
end;

// ****************************************************************************
//
//                        Exported  functions
//
// ****************************************************************************

function OpenDB : Boolean;
var fIni : TIniFile;
    s    : String;
begin
   FLogLevel := pub.RegGetInt('MikroLan','','LogLevel',0);
   fIni := TIniFile.Create('.\TDB.INI');
   FMySQL := TMySQLClient.Create;
   InitializeCriticalSection(FCriticalSection);
   try
      FMySQL.Host:= fIni.ReadString('Server','Host','localhost'); //'localhost';
      FMysql.port:= fIni.ReadInteger('Server','Port',3306); //3306;
      FMysql.user:= fIni.ReadString('Server', 'User', '');
      FMysql.password:= fIni.ReadString('Server', 'Password', '');
      FNodeName := fIni.ReadString('Node','Nodename','');
      FNodeDesc := fIni.ReadString('Node','Description','');
      FAlarmKeepTime:= fIni.ReadInteger('Node','KeepAlarms',30); //3306;
      s := fIni.ReadString('Server', 'SQLVersion', '4.0');
      FSQLVersion41 := s = '4.1';
      FMysql.Db:='thermometer';
      FMysql.UseNamedPipe:=FALSE;
      FMysql.UseSSL:=FALSE;
      FMysql.Compress:=TRUE;
      FMysql.TrySockets:=FALSE;

      if FMySQL.connect then
      begin
         if FLogLevel > 0 then
            pub.AddLog('DB open at host '+FMySQL.Host,'.\SQLLOG.txt',500);
         result := TRUE;
         FNodeID := DoGetNode(PChar(FNodeName));
         if FNodeID < 0 then
         begin
            DoInsertNode(PChar(FNodeName),PChar(FNodeDesc));
            FNodeID := DoGetNode(PChar(FNodeName));
            if FNodeID < 0 then
            begin
               if FLogLevel > 0 then
                  pub.AddLog('Error: Can not insert NODEID ','.\SQLLOG.txt',500);
               result := FALSE;
            end;
         end;
      end
      else
      begin
         if FLogLevel > 0 then
         begin
            pub.AddLog('Error: Can not open DB','.\SQLLOG.txt',500);
            pub.AddLog('Error: '+FMySQL.LastError,'.\SQLLOG.txt',500);
         end;
         result := FALSE;
      end;
   finally
      fIni.Free;
   end
end;

procedure CloseDB;
begin
   if FLogLevel > 0 then
      pub.AddLog('Closeing DB','.\SQLLOG.txt',500);

   if FMySQL.Connected then
      FMySQL.close;

   DeleteCriticalSection (FCriticalSection);
end;

function SaveTemp(pSensorname : PChar; iSensorID : Integer; value : Double) : Integer; stdcall; export;
begin
   EnterCriticalSection(FCriticalSection);
   result := DoSaveTemp(FNodeID,iSensorID,value,now);
   LeaveCriticalSection(FCriticalSection);
end;


procedure SaveAlarm(pAlarm : PChar; iSensorID, iAlarmType : Integer);  stdcall; export;
begin
{
   iAlarmType
   0 = No alarm
   1 = Low temp alarm
   2 = High temp alarm
   10 = Slope end
   11 = Falling slope
   12 = Raising slope
}
   EnterCriticalSection(FCriticalSection);
   DoInsertAlarm(FNodeID, iSensorID, iAlarmType, pAlarm, now);
   LeaveCriticalSection(FCriticalSection);
end;

procedure GetAlarms(slAlarms : TStrings); stdcall; export;
begin
   EnterCriticalSection(FCriticalSection);
   DoGetAlarms(FNodeID, slAlarms);
   LeaveCriticalSection(FCriticalSection);
end;


function CheckDate(pSensorname : PChar; iSensorID : Integer; DateOfData : TDateTime) : Boolean ; stdcall; export;
begin
   EnterCriticalSection(FCriticalSection);
   result := DoCheckDate(FNodeID, iSensorID, DateOfData);
   LeaveCriticalSection(FCriticalSection);
end;



function GetDayTemp(pSensorname : PChar; iSensorID : Integer; BeginDate, EndDate : TDateTime; DataList : TList; iMaxCount : Integer) : Integer; stdcall; export;
var DataRecord : pData;
    slData     : TStringList;
    i,
    iCurrentRecord  : Integer;
    fTemp      : Double;
    dtTmp      : TDateTime;
    cDS        : Char;        //Current DecimalSeparator
begin
   cDS := DecimalSeparator;
   EnterCriticalSection(FCriticalSection);
   if FLogLevel > 4 then
   begin
      pub.AddLog(String(pSensorName) + ': Loading period '+DateTimeToStr(BeginDate)+'-'+DateTimeToStr(EndDate),'.\SQLLOG.txt',500);
      pub.AddLog(String(pSensorName) + ': Records allocated ' + IntToStr(iMaxCount),'.\SQLLOG.txt',500);
   end;
   slData := TStringList.Create;
   DecimalSeparator := '.';
   result := 0;


   iCurrentRecord := DataList.Count - iMaxCount;
   try
     try
         DoGetTemp (FNodeID, iSensorID, BeginDate, EndDate);

         if FResult.RowsCount > 0 then
            for i := 0 to FResult.RowsCount  do
            begin
               if iMaxCount > 0 then
               begin
                  DecimalSeparator := '.';
                  fTemp := StrToFloat(FResult.FieldValueByName('data_temp')); //Temperature
                  DecimalSeparator := cDS;
{
                  dtTmp := SQLTimeStampToDateTime(
                           String(FResult.FieldValueByName('data_time')),  //Time  HHMMSS
                           String(FResult.FieldValueByName('data_date'))); //Date  YYYYMMDD
}
                  dtTmp := SQLTimeStampToDateTime(String(FResult.FieldValueByName('data_time'))); //Time  YYYYMMDDHHMMSS

                  DataRecord := DataList.Items[iCurrentRecord];
                  DataRecord^.tdTime := dtTmp;
                  DataRecord^.fTemp := fTemp;
                  Dec(iMaxCount);
                  Inc(iCurrentRecord);
               end
               else
                  Inc(result);
               FResult.Next;
            end;

         if FLogLevel > 4 then
         begin
            pub.AddLog(String(pSensorName) + ': Loaded '+IntToStr(iCurrentRecord)+' lines','.\SQLLOG.txt',500);
            pub.AddLog(String(pSensorName) + ': New records needed ' + IntToStr(result),'.\SQLLOG.txt',500);
         end;
      except
         on E : Exception do pub.AddLog ('Failed to fetch history'+Chr(13)+'Error: '+E.Message,'.\SQLLOG.txt',500);
      end;
   finally
      slData.Free;
   end;
   LeaveCriticalSection(FCriticalSection);
end;


exports
   OpenDB,
   CloseDB,
   SaveTemp,
   SaveAlarm,
   GetAlarms,
   CheckDate,
   GetDayTemp;
begin
end.



